<?php
/**
 * Created by PhpStorm.
 * User: renyi
 * Date: 2017/7/23
 * Time: 下午19:40
 */

namespace App\Helper;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redirect;
use App\Models\Mzy\MzyAppointment;
use App\Models\Mzy\Doctor;
use App\Models\Mzy\MzyDoctorSchedule;
use App\Models\Mzy\MzyStation;
use App\Models\Mzy\MzyStatVisit;
use App\Models\Mzy\MzyTeam;
use App\Models\Mzy\MzyTeamDoctor;
use App\Models\Mzy\MzyTeamStation;
use App\Models\Mzy\User;

class StatHelper
{
    public static function addModelWhere($user,$modelDb,$inFields=[])
    {
        $userType = self::getAdminType($user);

        if(!empty($inFields) && !empty($userType))
        {
            if(in_array($userType,$inFields))
            {
                if($userType == "team" || $userType == "station")
                {
                    $userType = "App_".$userType;
                }
                $modelDb->where("{$userType}.id",$user->tid);
            }
            else
            {
                $modelDb->whereRaw('1=0');
            }
        }
    }

    public static function addModelWhereAndReturn($user,$modelDb,$inFields=[])
    {
        $userType = self::getAdminType($user);

        if(!empty($inFields) && !empty($userType))
        {
            if(in_array($userType,$inFields))
            {
                if($userType == "team" || $userType == "station")
                {
                    $userType = "App_".$userType;
                }
                return $modelDb->where("{$userType}.id",$user->tid);
            }
            else
            {
                return $modelDb->whereRaw('1=0');
            }
        }
        return $modelDb;
    }

    public static function checkUserRole($user,$modelDb,$route)
    {
        $userType = self::getAdminType($user);

        if(!empty($userType))
        {
            $dataCount = $modelDb->where("{$userType}.id",'=', $user->tid)->count();
            if($dataCount == 0)
            {
                return Redirect::to("/door/report/{$route}");
            }
        }
    }

    public static function getAdminType($user)
    {
        if($user->isRole('hospital'))
        {
              $type = 'hospital';
        }
        elseif($user->isRole('team'))
        {
            $type = 'team';
        }
        elseif($user->isRole('county'))
        {
            $type = 'county';
        }
        elseif($user->isRole('station'))
        {
            $type = 'station';
        }
        else
        {
            $type = '';
        }

        return $type;
    }

    public static function getScheduleCount($type,$tid)
    {
        return self::getCommonVisitCount($type,$tid,null);
    }

    public static function getVisitCount($type,$tid)
    {
        return self::getCommonVisitCount($type,$tid,1);
    }

    public static function getUnvisitCount($type,$tid)
    {
        return self::getCommonVisitCount($type,$tid,0);
    }

    private static function getCommonVisitCount($type,$tid,$status)
    {
        $scheduleDb = MzyDoctorSchedule::join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                                    ->join('hospital','hospital.id','=','App_team.hospital_id')
                                    ->join('App_station','App_station.id','=','App_doctor_schedule.station_id')
                                    ->join('county','county.id','=','App_station.county_id')
                                    ->join('doctor','doctor.id','=','App_doctor_schedule.doctor_id');

        if($type =='team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type =='hospital')
        {
            $scheduleDb->where('App_team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $scheduleDb->where('App_station.id',$tid);
        }
        elseif($type == 'doctor')
        {
            $scheduleDb->where('doctor.id',$tid);
        }

        $scheduleDb->where('doctor.teacher_id','=',0);

        if($status !== null)
        {
            $scheduleDb->where('App_doctor_schedule.work_date','<',DateHelper::day())
                       ->where('App_doctor_schedule.work_date','>=','2018-06-11')
                       ->where('App_doctor_schedule.status',$status);
        }

        $visitCount = $scheduleDb->count();

        return $visitCount;
    }

    public static function getDoctorSql($target, $dateRangeStart = '', $dateRangeEnd = '')
    {
        $targetName = str_replace('App_', '', $target);
        $dateRange = '';
        if ($dateRangeStart != '')
        {
            $dateRange = $dateRangeStart;
        }
        if ($dateRangeEnd != '')
        {
            $dateRange = $dateRangeEnd;
        }
        $doctorSql = "(SELECT count(DISTINCT(tdoctor.id))
                     FROM App_team_station
                     JOIN App_team_doctor ON App_team_doctor.team_id=App_team_station.team_id
                     JOIN doctor as tdoctor ON tdoctor.id=App_team_doctor.doctor_id
                     JOIN App_station as tstation on tstation.id=App_team_station.station_id
                     JOIN App_team as tteam on tteam.id=App_team_station.team_id
                     where tdoctor.teacher_id=0 AND t{$targetName}.id={$target}.id) as doctor_count";

        if ($dateRange != '')
        {
            $dateRange = $dateRange . ' 00:00:00';
            $doctorSql = "(SELECT count(DISTINCT(tdoctor.id))
                         FROM App_team_station
                         JOIN App_team_doctor ON App_team_doctor.team_id=App_team_station.team_id
                         JOIN doctor as tdoctor ON tdoctor.id=App_team_doctor.doctor_id
                         JOIN App_station as tstation on tstation.id=App_team_station.station_id
                         JOIN App_team as tteam on tteam.id=App_team_station.team_id
                         where tdoctor.teacher_id=0 AND t{$targetName}.id={$target}.id AND tdoctor.created_at <= '{$dateRange}') as doctor_count";
        }

        return DB::Raw($doctorSql);
    }

    public static function getGroupSql($type,$target)
    {
        $typeName = str_replace('App_', '', $type);
        $targetName = str_replace('App_', '', $target);

        $dbNameList = [$typeName,$targetName];
        rsort($dbNameList);

        $dbName = 'App_'.join('_',$dbNameList);

        $groupSql = "(SELECT group_concat({$type}.name separator ';')
                      FROM {$dbName}
                      JOIN {$type} ON {$type}.id={$dbName}.{$typeName}_id
                      WHERE {$dbName}.{$targetName}_id={$target}.id
                      GROUP BY {$dbName}.{$targetName}_id) as {$typeName}_names";

        return DB::Raw($groupSql);
    }

    public static function getVisitSql($type,$target)
    {
        $curDate = DateHelper::day();
        $visitSql = " FROM App_doctor_schedule as tds
                     JOIN App_team     AS tt ON tt.id=tds.team_id
                     JOIN hospital AS th ON th.id=tt.hospital_id
                     JOIN App_station  AS ts ON ts.id=tds.station_id
                     JOIN county   AS tc ON tc.id=ts.county_id
                     JOIN doctor   AS td ON td.id=tds.doctor_id
                     WHERE tds.work_date<'{$curDate}' AND td.teacher_id=0 AND tds.{$target}_id={$target}.id";

        if($type=='visit')
        {
            $visitSql = "(SELECT count(1)" . $visitSql . " AND tds.status>0) as visit_count";
        }
        else if($type=='unvisit')
        {
            $visitSql = "(SELECT count(1)" . $visitSql . " AND tds.status=0) as unvisit_count";
        }
        else if($type=='patient')
        {
            $visitSql = "(SELECT sum(visit_patient_count)" . $visitSql . " AND tds.status>0) as visit_patient_count";
        }
        else if($type=='recipe')
        {
            $visitSql = "(SELECT sum(visit_recipe_count)" . $visitSql . " AND tds.status>0) as visit_recipe_count";
        }

        return DB::Raw($visitSql);
    }

    public static function getScanCount()
    {
        return User::count();
    }

    //取得就诊人数
    public static function getVisitPatientCount($type,$tid)
    {
        $scheduleDb = MzyDoctorSchedule::join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                                    ->join('hospital','hospital.id','=','App_team.hospital_id')
                                    ->join('App_station','App_station.id','=','App_doctor_schedule.station_id')
                                    ->join('county','county.id','=','App_station.county_id')
                                    ->join('doctor','doctor.id','=','App_doctor_schedule.doctor_id');

        if($type == 'team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $scheduleDb->where('App_team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $scheduleDb->where('station_id',$tid);
        }
        elseif($type == 'doctor')
        {
            $scheduleDb->where('doctor_id',$tid);
        }

        $scheduleDb->where('doctor.teacher_id','=',0);

        $visitPatientCount = $scheduleDb->where('App_doctor_schedule.work_date','<',DateHelper::day())
                                        ->where('App_doctor_schedule.work_date','>=','2018-06-11')
//                                        ->where('App_doctor_schedule.status',1)
                                        ->sum('App_doctor_schedule.visit_patient_count');

        return $visitPatientCount;
    }

    public static function getAppointmentCount($type,$tid)
    {
        $appointmentDb = MzyAppointment::join('patient','patient.id','=','App_appointment.patient_id')
                      ->join('App_doctor_schedule','App_doctor_schedule.id','=','App_appointment.schedule_id')
                      ->join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                      ->join('hospital','hospital.id','=','App_team.hospital_id')
                      ->join('App_station','App_station.id','=','App_doctor_schedule.station_id')
                      ->join('county','county.id','=','App_station.county_id')
                      ->join('doctor','doctor.id','=','App_doctor_schedule.doctor_id');

        if($type == 'team')
        {
            $appointmentDb->where('App_doctor_schedule.team_id',$tid);

        }
        elseif($type == 'county')
        {
            $appointmentDb ->where('App_station.county_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $appointmentDb->where('App_team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $appointmentDb->where('App_doctor_schedule.station_id',$tid);
        }
        elseif($type == 'doctor')
        {
            $appointmentDb->where('App_doctor_schedule.doctor_id',$tid);
        }

        $appointmentDb->where('doctor.teacher_id','=',0);
        $appointmentDb->where('App_appointment.status','=',0);

        $appointmentCount = $appointmentDb->count();

        return $appointmentCount;
    }

    public static function getDoctorCount($type,$tid)
    {
        if($type == 'team')
        {
            $doctorDb = MzyTeamDoctor::where('team_id',$tid)
                                  ->join('doctor','doctor.id','=','App_team_doctor.doctor_id');

        }
        elseif($type == 'county')
        {
            $doctorDb = MzyTeamStation::join('App_team_doctor','App_team_doctor.team_id','=','App_team_station.team_id')
                                   ->join('doctor','doctor.id','=','App_team_doctor.doctor_id')
                                   ->join('App_station','App_station.id','=','App_team_station.station_id')
                                   ->where('App_station.county_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $doctorDb = MzyTeamDoctor::join('App_team','App_team.id','=','App_team_doctor.team_id')
                                  ->join('doctor','doctor.id','=','App_team_doctor.doctor_id')
                                  ->where('team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $doctorDb = MzyTeamStation::join('App_team_doctor','App_team_doctor.team_id','=','App_team_station.team_id')
                                   ->join('doctor','doctor.id','=','App_team_doctor.doctor_id')
                                   ->where('App_team_station.station_id',$tid);
        }
        else
        {
            $doctorDb = Doctor::where('id','>',0);
        }

        $doctorDb->where('doctor.teacher_id','=',0);

        $doctorCount = $doctorDb->count();

        return $doctorCount;
    }

    //
    public static function getWorkStatusStr($workDate,$workStatus, $visitWorkTime = 0)
    {
        // 出诊情况
        if($workDate >= DateHelper::day())
        {
            $status = '-';
        }
        elseif($workStatus ==1)
        {
            $status = '正常出诊';
            if ($visitWorkTime != 0)
            {
                switch ($visitWorkTime)
                {
                    case 1:
                        $status = '正常出诊（上午）';
                        break;
                    case 2:
                        $status = '正常出诊（下午）';
                        break;
                    case 3:
                        $status = '正常出诊（全天）';
                        break;
                    default:
                        break;
                }
            }
        }
        else
        {
            $status = '<p style="color:red">出诊异常</p>';
        }

        return $status;
    }

    //
    public static function getVisitCountStr($workStatus,$count)
    {
        if($workStatus == 0)
        {
            // 患者人数
            $countStr = '<p style="color:red">-</p>';
        }
        else
        {
            $countStr = $count;
        }

        return $countStr;
    }

    //团队数
    public static function getTeamCount($hospitalId)
    {
        if($hospitalId == 0)
        {
            return MzyTeam::count();
        }
        else
        {
            return MzyTeam::where('hospital_id',$hospitalId)->count();
        }

    }

    //社区数
    public static function getStationCount($countyId)
    {
        if($countyId == 0)
        {
            return MzyStation::count()-1;
        }
        else
        {
            return MzyStation::where('county_id',$countyId)->count();
        }
    }

    //明日出勤
    public static function getTommorrowCount()
    {
        return MzyDoctorSchedule::where('work_date',date('Y-m-d',strtotime('+1 day')))->count();
    }



    // 获取总缺勤数（从 >= 2018年6月11日星期一至今） 缺勤：每周应出勤 1 天，本周缺少的出勤天数
    public static function getAllUnvisitCount($type,$tid)
    {
        $weekStartDate = '2018-06-11';
        $scheduleDb = MzyStatVisit::where('week_start_date','>=',$weekStartDate);

        if($type =='station')
        {
            $scheduleDb->where('station_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->join('App_station','App_station.id','=','App_stat_visit.station_id');
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type == 'team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $scheduleDb->join('App_team','App_team.id','=','App_stat_visit.team_id');
            $scheduleDb->where('App_team.hospital_id',$tid);
        }

        $visitCount = $scheduleDb->where('App_stat_visit.week_end_date','<',DateHelper::day())
                    ->sum('day_count_absent');

        return $visitCount;
    }

    // 获取本月缺勤天数
    public static function getMonthUnvisitCount($type,$tid)
    {
        $scheduleDb = MzyStatVisit::where('App_stat_visit.week_end_date','<',DateHelper::day());

        if($type =='station')
        {
            $scheduleDb->where('station_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->join('App_station','App_station.id','=','App_stat_visit.station_id');
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type == 'team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $scheduleDb->join('App_team','App_team.id','=','App_stat_visit.team_id');
            $scheduleDb->where('App_team.hospital_id',$tid);
        }

        $nowTime = time();
        $toDay = date('d', $nowTime);
        if ($toDay <= 25)
        {
            $visitMonth = date('Y-m', $nowTime);
            $visitMonth = $visitMonth.'-25';
        } else {
            $visitMonth = date('Y-m', $nowTime + 86400*6);
            $visitMonth = $visitMonth.'-25';
        }

        $visitCount = $scheduleDb->where('visit_month', $visitMonth)->sum('day_count_absent');

        return $visitCount;
    }


    // 获取应出勤天数（总数）应出勤：每周出勤 1 天
    public static function getAllScheduleCount($type,$tid)
    {
        $weekStartDate = '2018-06-11';
        $scheduleDb = MzyStatVisit::where('week_start_date','>=',$weekStartDate);

        if($type =='station')
        {
            $scheduleDb->where('station_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->join('App_station','App_station.id','=','App_stat_visit.station_id');
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type == 'team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $scheduleDb->join('App_team','App_team.id','=','App_stat_visit.team_id');
            $scheduleDb->where('App_team.hospital_id',$tid);
        }

        $visitCount = $scheduleDb->where('App_stat_visit.week_end_date','<',DateHelper::day())
                    ->sum('day_count_schedule');

        return $visitCount;
    }


    // 获取总出诊天数(实际出勤)（从 >= 2018年6月11日星期一至今）
    public static function getAllVisitWorkTimeCount($type,$tid)
    {
        return self::getCommonVisitWorkTimeCount($type,$tid);
    }

    // 获取本月出诊天数(实际出勤)
    public static function getMonthVisitWorkTimeCount($type,$tid)
    {
        return self::getCommonVisitWorkTimeCount($type,$tid,1);
    }

    // 出诊天数(实际出勤天数)公共方法
    private static function getCommonVisitWorkTimeCount($type,$tid,$getThisMonth = null)
    {

        $scheduleDb = MzyDoctorSchedule::where('App_doctor_schedule.visit_work_date','>=','2018-06-11')
                    ->where('App_doctor_schedule.visit_work_date','<',DateHelper::day())
                    ->where('App_doctor_schedule.status',1);

        if($type =='team')
        {
            $scheduleDb->where('App_doctor_schedule.team_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->join('App_station','App_station.id','=','App_doctor_schedule.station_id')
                       ->where('App_station.county_id',$tid);
        }
        elseif($type =='hospital')
        {
            $scheduleDb->join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                       ->where('team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $scheduleDb->where('App_doctor_schedule.station_id',$tid);
        }
        elseif($type == 'doctor')
        {
            $scheduleDb->where('App_doctor_schedule.visit_doctor_id',$tid);
        }

        if($getThisMonth !== null)
        {
            $rangeDate = DateHelper::getRangeDate();
            $scheduleDb->where('App_doctor_schedule.visit_work_date','>=',$rangeDate[0]);
            $scheduleDb->where('App_doctor_schedule.visit_work_date','<',$rangeDate[1]);
        }
        $scheduleList = $scheduleDb->get();
        $visitCount = 0;
        foreach ($scheduleList as $v)
        {
            switch ($v->visit_work_time)
            {
                case 1:
                    $visitCount = $visitCount + 0.5;
                    break;
                case 2:
                    $visitCount = $visitCount + 0.5;
                    break;
                case 3:
                    $visitCount = $visitCount + 1;
                    break;
                default:
                    # code...
                    break;
            }
        }

        return $visitCount;
    }


    // 取得本月就诊人数(本月患者数)
    public static function getMonthVisitPatientCount($type,$tid)
    {
        $scheduleDb = MzyDoctorSchedule::join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                                    ->join('hospital','hospital.id','=','App_team.hospital_id')
                                    ->join('App_station','App_station.id','=','App_doctor_schedule.station_id')
                                    ->join('county','county.id','=','App_station.county_id')
                                    ->join('doctor','doctor.id','=','App_doctor_schedule.doctor_id');

        if($type == 'team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $scheduleDb->where('App_team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $scheduleDb->where('station_id',$tid);
        }
        elseif($type == 'doctor')
        {
            $scheduleDb->where('doctor_id',$tid);
        }

        $scheduleDb->where('doctor.teacher_id','=',0);

        $rangeDate = DateHelper::getRangeDate();
        $monthVisitPatientCount = $scheduleDb->where('App_doctor_schedule.work_date','>=',$rangeDate[0])
                                        ->where('App_doctor_schedule.work_date','<',$rangeDate[1])
                                        ->where('App_doctor_schedule.status',1)
                                        ->sum('App_doctor_schedule.visit_patient_count');

        return $monthVisitPatientCount;
    }


    // 取得处方总数
    public static function getVisitRecipeCount($type,$tid)
    {
        $scheduleDb = MzyDoctorSchedule::join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                                    ->join('hospital','hospital.id','=','App_team.hospital_id')
                                    ->join('App_station','App_station.id','=','App_doctor_schedule.station_id')
                                    ->join('county','county.id','=','App_station.county_id')
                                    ->join('doctor','doctor.id','=','App_doctor_schedule.doctor_id');

        if($type == 'team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $scheduleDb->where('App_team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $scheduleDb->where('station_id',$tid);
        }
        elseif($type == 'doctor')
        {
            $scheduleDb->where('doctor_id',$tid);
        }

        $scheduleDb->where('doctor.teacher_id','=',0);

        $visitRecipeCount = $scheduleDb->where('App_doctor_schedule.work_date','<',DateHelper::day())
                                       ->where('App_doctor_schedule.work_date','>=','2018-06-11')
//                                            ->where('doctor_schedule.status',1)
                                            ->sum('App_doctor_schedule.visit_recipe_count');

        return $visitRecipeCount;
    }

    // 取得本月处方数
    public static function getMonthVisitRecipeCount($type,$tid)
    {
        $scheduleDb = MzyDoctorSchedule::join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                                    ->join('hospital','hospital.id','=','App_team.hospital_id')
                                    ->join('App_station','App_station.id','=','App_doctor_schedule.station_id')
                                    ->join('county','county.id','=','App_station.county_id')
                                    ->join('doctor','doctor.id','=','App_doctor_schedule.doctor_id');

        if($type == 'team')
        {
            $scheduleDb->where('team_id',$tid);
        }
        elseif($type == 'county')
        {
            $scheduleDb->where('App_station.county_id',$tid);
        }
        elseif($type == 'hospital')
        {
            $scheduleDb->where('App_team.hospital_id',$tid);
        }
        elseif($type == 'station')
        {
            $scheduleDb->where('station_id',$tid);
        }
        elseif($type == 'doctor')
        {
            $scheduleDb->where('doctor_id',$tid);
        }

        $scheduleDb->where('doctor.teacher_id','=',0);

        $rangeDate = DateHelper::getRangeDate();
        $visitRecipeCount = $scheduleDb->where('App_doctor_schedule.work_date','>=',$rangeDate[0])
                                       ->where('App_doctor_schedule.work_date','<',$rangeDate[1])
                                       ->where('App_doctor_schedule.status',1)
                                       ->sum('App_doctor_schedule.visit_recipe_count');

        return $visitRecipeCount;
    }


    // 拼接获取统计数据的sql
    public static function getStatVisitSql($type,$target, $dateRangeStart = '', $dateRangeEnd = '')
    {
        $targetName = str_replace('App_', '', $target);
        $curDate = DateHelper::day();
        $visitSql = " FROM App_stat_visit as svs
                     JOIN App_team     AS tt ON tt.id=svs.team_id
                     JOIN hospital AS th ON th.id=tt.hospital_id
                     JOIN App_station  AS ts ON ts.id=svs.station_id
                     JOIN county   AS tc ON tc.id=ts.county_id
                     WHERE svs.week_end_date<'{$curDate}' AND svs.{$targetName}_id={$target}.id";

        if ($dateRangeStart != '')
        {
            $visitSql = $visitSql . ' AND svs.week_start_date >= "' . $dateRangeStart . '"';
        }
        if ($dateRangeEnd != '')
        {
            $visitSql = $visitSql . ' AND svs.week_end_date <= "' . $dateRangeEnd . '"';
        }

        if($type=='visit')
        {
            $visitSql = "(SELECT IF(sum(svs.`day_count_visit`)>0,sum(svs.`day_count_visit`),0)" . $visitSql . " ) as visit_count";
        }
        else if($type=='unvisit')
        {
            $visitSql = "(SELECT IF(sum(svs.`day_count_absent`)>0,sum(svs.`day_count_absent`),0)" . $visitSql . " ) as unvisit_count";
        }
        else if($type=='patient')
        {
            $visitSql = "(SELECT IF(sum(svs.`patient_count`)>0,sum(svs.`patient_count`),0)" . $visitSql . " ) as visit_patient_count";
        }
        else if($type=='recipe')
        {
            $visitSql = "(SELECT IF(sum(svs.`recipe_count`)>0,sum(svs.`recipe_count`),0)" . $visitSql . " ) as visit_recipe_count";
        }
        else if($type=='zhiweibing')
        {
            $visitSql = "(SELECT IF(sum(svs.`zhiweibing_count`)>0,sum(svs.`zhiweibing_count`),0)" . $visitSql . " ) as visit_zhiweibing_count";
        }
        else if($type=='schedule')
        {
            $visitSql = "(SELECT IF(sum(svs.`day_count_schedule`)>0,sum(svs.`day_count_schedule`),0)" . $visitSql . " ) as schedule_count";
        }

        return DB::Raw($visitSql);
    }


    // 获取医生的出勤缺勤sql
    public static function getDoctorVisitSql($type,$target, $dateRangeStart = '', $dateRangeEnd = '')
    {
        $targetName = str_replace('App_', '', $target);
        $curDate = DateHelper::day();
        $visitSql = " FROM App_doctor_schedule as tds
                     JOIN App_team     AS tt ON tt.id=tds.team_id
                     JOIN hospital AS th ON th.id=tt.hospital_id
                     JOIN App_station  AS ts ON ts.id=tds.station_id
                     JOIN county   AS tc ON tc.id=ts.county_id
                     JOIN doctor   AS td ON td.id=tds.doctor_id
                     WHERE tds.work_date<'{$curDate}' AND td.teacher_id=0 AND tds.{$targetName}_id={$target}.id AND tds.work_date>='2018-06-11' ";

        if ($dateRangeStart != '')
        {
            $visitSql = $visitSql . ' AND tds.work_date >= "' . $dateRangeStart . '"';
        }
        if ($dateRangeEnd != '')
        {
            $visitSql = $visitSql . ' AND tds.work_date <= "' . $dateRangeEnd . '"';
        }

        if($type=='visit')
        {
            $visitSql = "((SELECT count(1)" . $visitSql . " AND tds.status>0 AND tds.visit_work_time=1)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.status>0 AND tds.visit_work_time=2)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.status>0 AND tds.visit_work_time=3)) as visit_count";
        }
        elseif($type=='unvisit')
        {
            // $visitSql = "((SELECT count(0.5)" . $visitSql . " AND tds.status=0 AND tds.visit_work_time=1) + (SELECT count(0.5)" . $visitSql . " AND tds.status=0 AND tds.visit_work_time=2) + (SELECT count(1)" . $visitSql . " AND tds.status=0 AND tds.visit_work_time=3)) as unvisit_count";
            $visitSql = " (((SELECT count(1)" . $visitSql . " AND tds.work_time=1)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.work_time=2)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.work_time=3))-((SELECT count(1)" . $visitSql . " AND tds.status>0 AND tds.visit_work_time=1)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.status>0 AND tds.visit_work_time=2)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.status>0 AND tds.visit_work_time=3))) as unvisit_count";
        }
        elseif($type=='schedule')
        {
            $visitSql = "((SELECT count(1)" . $visitSql . " AND tds.work_time=1)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.work_time=2)*0.5 + (SELECT count(1)" . $visitSql . " AND tds.work_time=3)) as schedule_count";
        }
        elseif($type=='patient')
        {
            $visitSql = "(SELECT sum(visit_patient_count)" . $visitSql . " AND tds.status>0) as visit_patient_count";
        }
        elseif($type=='recipe')
        {
            $visitSql = "(SELECT sum(visit_recipe_count)" . $visitSql . " AND tds.status>0) as visit_recipe_count";
        }
        elseif($type=='zhiweibing')
        {
            $visitSql = "(SELECT sum(visit_zhiweibing_count)" . $visitSql . " AND tds.status>0) as visit_zhiweibing_count";
        }

        return DB::Raw($visitSql);
    }

    // 获取医生的应出勤天数、实际出勤天数、缺勤天数
    public static function getDoctorVisitCount($tid, $type='')
    {
        $scheduleDb = MzyDoctorSchedule::join('App_team','App_team.id','=','App_doctor_schedule.team_id')
                                    ->join('doctor','doctor.id','=','App_doctor_schedule.doctor_id')
                                    ->where('doctor.id',$tid)
                                    ->where('doctor.teacher_id','=',0)
                                    ->where('App_doctor_schedule.work_date','<',DateHelper::day())
                                    ->where('App_doctor_schedule.work_date','>','2018-06-11');

        $scheduleList   = $scheduleDb->get();
        // 应出勤天数
        $scheduleCount  = 0;
        // 实际出勤天数
        $visitCount     = 0;
        // 缺勤天数
        $unvisitCount   = 0;
        foreach ($scheduleList as $v)
        {
            if ($v->status > 0)
            {
                switch ($v->visit_work_time)
                {
                    case 1:
                        $visitCount = $visitCount + 0.5;
                        break;
                    case 2:
                        $visitCount = $visitCount + 0.5;
                        break;
                    case 3:
                        $visitCount = $visitCount + 1;
                        break;
                    default:
                        # code...
                        break;
                }
            }
            switch ($v->work_time)
            {
                case 1:
                    $scheduleCount = $scheduleCount + 0.5;
                    break;
                case 2:
                    $scheduleCount = $scheduleCount + 0.5;
                    break;
                case 3:
                    $scheduleCount = $scheduleCount + 1;
                    break;
                default:
                    # code...
                    break;
            }
        }

        $unvisitCount = $scheduleCount - $visitCount;
        if ($unvisitCount < 0)
        {
            $unvisitCount = 0;
        }

        switch ($type) {
            case 'schedule':
                return $scheduleCount;
                break;
            case 'visit':
                return $visitCount;
                break;
            case 'unvisit':
                return $unvisitCount;
                break;
            default:
                return 0;
                break;
        }

    }

    // 出诊出勤数字标示转为文字
    public static function getWorkTimeText($workTime)
    {
        $textStr = '';
        switch ($workTime) {
            case 1:
                $textStr = '上午';
                break;
            case 2:
                $textStr = '下午';
                break;
            case 3:
                $textStr = '全天';
                break;
            default:
                $textStr = ' ';
                break;
        }
        return $textStr;
    }

    // 出诊情况显示文字处理
    public static function getVisitWorkStatusText($workStatus, $workDate, $visitWorkDate, $visitWorkTime, $doctorName, $isEditVisit)
    {
        $textStr = '';
        // 出诊情况
        if($workDate >= DateHelper::day())
        {
            $textStr = '-';
        }
        elseif(($workStatus == 1) &&($visitWorkDate != '1970-01-01') && ($visitWorkTime != 0))
        {
            switch ($visitWorkTime)
            {
                case 1:
                    $textStr = $visitWorkDate . '&nbsp;&nbsp;&nbsp;上午&nbsp;&nbsp;&nbsp;' . $doctorName;
                    break;
                case 2:
                    $textStr = $visitWorkDate . '&nbsp;&nbsp;&nbsp;上午&nbsp;&nbsp;&nbsp;' . $doctorName;
                    break;
                case 3:
                    $textStr = $visitWorkDate . '&nbsp;&nbsp;&nbsp;上午&nbsp;&nbsp;&nbsp;' . $doctorName;
                    break;
                default:
                    $textStr = '';
                    break;
            }
        }
        else
        {
            $textStr = '<p style="color:red">未出诊</p>';
            if ($isEditVisit == 0)
            {
                $textStr = '<p style="color:red">未记录</p>';
            }
        }

        return $textStr;
    }

}
